package com.ruoyi.screen.repository;

import com.ruoyi.screen.dto.ServiceUsageDTO;
import com.ruoyi.screen.dto.WeeklyUsageDTO;
import com.ruoyi.screen.entity.UserService;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;
import java.util.List;

public interface UserServiceRepository extends JpaRepository<UserService, Long> {
    @Query("SELECT COUNT(us) FROM UserService us WHERE us.openTime >= :startDate AND us.openTime < :endDate")
    Long countByOpenTimeBetween(@Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate);

    @Query("SELECT new com.ruoyi.screen.dto.ServiceUsageDTO(st.name, COUNT(us)) " +
            "FROM UserService us JOIN ServiceType st ON us.serviceId = st.id " +
            "WHERE us.openTime >= :startDate AND us.openTime < :endDate " +
            "GROUP BY st.name")
    List<ServiceUsageDTO> countServiceUsageByWeek(@Param("startDate") LocalDateTime startDate,
                                                  @Param("endDate") LocalDateTime endDate);

    @Query("SELECT new com.ruoyi.screen.dto.WeeklyUsageDTO(FUNCTION('DATE_FORMAT', us.openTime, '%Y-%u'), COUNT(us)) " +
            "FROM UserService us WHERE us.serviceId = :serviceId " +
            "GROUP BY FUNCTION('DATE_FORMAT', us.openTime, '%Y-%u')")
    List<WeeklyUsageDTO> countWeeklyUsageByService(@Param("serviceId") Integer serviceId);
}